给sql语句添加分页（oracle） |
您所在的位置：网站首页 › justhost vs ipage › 给sql语句添加分页（oracle） |
|
方法一：sql直接拼接
server
Map page = new HashMap();
page.put("ROWNUM",Integer.parseInt(request.getPage())*Integer.parseInt(request.getLimit()));
page.put("RN",Integer.parseInt(request.getPage())-1);
List orderList = dao.getQuery(page,param);
Dao
ListgetQuery(Map page, Map param);
mapper
SELECT *
FROM (SELECT TMP.*, ROWNUM ROW_ID
FROM (
select
t.ID as ID,
t.NAME as NAME
from stable t
where 1 = 1
and to_char(time, 'yyyy-mm-dd') to_char(to_date(#{param.endtime}, 'yyyy-mm-dd'),'yyyy-mm-dd')
and to_char(time, 'yyyy-mm-dd') >= to_char(to_date(#{param.starttime}, 'yyyy-mm-dd'),'yyyy-mm-dd')
order by time desc
) TMP
WHERE ROWNUM #{page.ROWNUM})
WHERE ROW_ID > #{page.RN}
注意上面的sql中，select 的字段要重新定义参数，不然sql报错 方法二：使用mybatis-plus的iPage封装sql Service //import com.baomidou.mybatisplus.core.metadata.IPage; //import com.baomidou.mybatisplus.extension.plugins.pagination.Page; Page page = new Page(Integer.parseInt(request.getPage()),Integer.parseInt(request.getLimit())); IPage iPage = dao.getQuery(page,param); List list = iPage.getRecords(); Dao //import org.apache.ibatis.annotations.Mapper; //import org.apache.ibatis.annotations.Param; //import com.baomidou.mybatisplus.core.mapper.BaseMapper; //import com.baomidou.mybatisplus.core.metadata.IPage; //import com.baomidou.mybatisplus.extension.plugins.pagination.Page; //@Mapper //public interface ResourceDao extends BaseMapper { IPage getQuery(Page page, @Param("param") Map param); mapper select t.ID as ID, t.NAME as NAME from stable t where 1 = 1 and to_char(TIME, 'yyyy-mm-dd') to_char(to_date(#{param.endtime}, 'yyyy-mm-dd'),'yyyy-mm-dd') and to_char(TIME, 'yyyy-mm-dd') >= to_char(to_date(#{param.starttime}, 'yyyy-mm-dd'),'yyyy-mm-dd') order by TIME desc注意实体对象与表结构字段一一对应，会报错，注意参数格式 方法三：使用mybatis-plus的 QueryWrapper 直接拼接 Contoller //import com.xxx.common.utils.PageUtils; 自定义封装的page工具类 @RequestMapping("/list") public R list(@RequestParam Map params){ try{ PageUtils page = thService.queryPage(params); logger.info("${comments}: 成功"); return R.ok().put("page", page); }catch (Exception e){ logger.error("${comments}: 失败:",e); return R.error(); } } Service //import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; //import com.baomidou.mybatisplus.core.metadata.IPage; //import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; //import com.xxx.common.utils.Query; 自定义封装的Query工具类 //mport com.xxx.common.utils.PageUtils; 自定义封装的Query工具类 @Override public PageUtils queryPage(Map params) { IPage page = this.page( new Query().getPage(params), new QueryWrapper() .eq( "STATE","0") .apply( params.containsKey("createTimeStart")," CREATE_TIME BETWEEN to_date('"+params.get("createTimeStart")+"', 'yyyy-mm-dd') AND to_date('"+params.get("createTimeEnd")+"', 'yyyy-mm-dd')") .orderByDesc("CREATE_TIME") ); return new PageUtils(page); }params内封装了分页参数（PAGE，LIMIT），自定义封装工具完成进一步的调用 相关pom依赖 com.baomidou mybatis-plus-boot-starter ${mybatisplus.version} com.baomidou mybatis-plus-generator com.baomidou mybatis-plus ${mybatisplus.version} mysql mysql-connector-java ${mysql.version} com.oracle ojdbc6 ${oracle.version} |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |